Сводная таблица
Сводная таблица (англ. Pivot table) — инструмент обработки данных, служащий для их обобщения.
Этот инструмент используется, прежде всего, в программах визуализации данных, таких как электронные таблицы или программное обеспечение для бизнес-анализа. Кроме того, сводная таблица может автоматически сортировать, рассчитывать суммы или получить среднее значение из данных, записанных в электронной таблице. Она отображает результаты во второй таблице (называемой «сводной таблицей») в виде суммированных данных. Обычно пользователь настраивает и изменяет структуру сводной таблицы простым перетаскиванием элементов в графическом режиме.
Термин «сводная таблица» используется различными производителями. В США корпорация Microsoft владела (до 3 апреля 2020 г.) торговой маркой конкретного вида сводной таблицы[1]. Сводные таблицы можно рассматривать также как некое упрощение концепции OLAP.
История
[править | править код]Первое упоминание о сводных таблицах было в книге «Анализ данных в сводных таблицах» авторов Билла Елена и Майка Александера[2]. Основателем термина «сводная таблица» считается Пито Салас. Он разработал первую программу (Lotus Improv), которая помогала пользователям увидеть закономерности в данных электронных таблиц для быстрого построения моделей данных. В этой программе пользователь мог определять и сохранять наборы категорий, затем изменять представление с помощью перетаскивания категории мышкой. Эта особенность, в дальнейшем, превратилась в базовую для сводных таблиц. Lotus Development выпустила программу в 1991 году на платформе NeXT. Несколько месяцев спустя, технология появилась на компьютерах Mac и называлась DataPivot[3]. Компания Borland купила технологию DataPivot в 1992 году и внедрила её в свой проект электронных таблиц (Quattro Pro). В 1993 году, во время того, как появилась версия Improv. Эта функциональность была значительно улучшена в последующих версиях Microsoft Excel:
- Excel 97 включал в себя PivotTable Wizard, который позволял разработчикам писать макросы на языке Visual Basic для создания или модификации сводных таблиц.
- Excel 2000 представил технологию Pivot Charts для графического представления табличных данных.
Пример сводной таблицы
[править | править код]Типичное представление и хранение данных представляет собой плоскую таблицу. Это означает, что таблица содержит только столбцы (атрибуты) и строки (кортежи соответствующих значений атрибутов), как в нижеприведенном примере, показывающем данные по типам одежды:
| Region | Gender | Style | Ship date | Units | Price | Cost |
|---|---|---|---|---|---|---|
| East | Boy | Tee | 1/31/2005 | 12 | 11.04 | 10.42 |
| East | Boy | Golf | 1/31/2005 | 12 | 13 | 12.6 |
| East | Boy | Fancy | 1/31/2005 | 12 | 11.96 | 11.74 |
| East | Girl | Tee | 1/31/2005 | 10 | 11.27 | 10.56 |
| East | Girl | Golf | 1/31/2005 | 10 | 12.12 | 11.95 |
| East | Girl | Fancy | 1/31/2005 | 10 | 13.74 | 13.33 |
| West | Boy | Tee | 1/31/2005 | 11 | 11.44 | 10.94 |
| West | Boy | Golf | 1/31/2005 | 11 | 12.63 | 11.73 |
| West | Boy | Fancy | 1/31/2005 | 11 | 12.06 | 11.51 |
| West | Girl | Tee | 1/31/2005 | 15 | 13.42 | 13.29 |
| West | Girl | Golf | 1/31/2005 | 15 | 11.48 | 10.67 |
В то время, как такие таблицы могут содержать большое количество данных, довольно сложно воспринимать какую-то резюмированную информацию по ним. Сводная таблица может быстро объединять данные и выделять нужную информацию. Использование сводных таблиц очень распространено и зависит от ситуации. Первый вопрос, который надо задать перед составлением сводной таблицы: «Что мне нужно получить?» В нижеприведенном примере, вопрос может быть сформулирован следующим образом: «Сколько единиц продукции мы продали в каждом регионе для каждой даты поставки?».
Сводная таблица обычно содержит строки, колонки и поля данных (или фактов). В этом случае, столбец — Ship Date, строка — Region. Нужные нам данные — это сумма единиц продукции (sum of Units). Эти поля допускают несколько видов функций агрегации, включая суммирование, нахождение среднего, стандартное отклонение, количество элементов и другие. В нашем случае, суммарное количество поставленных единиц продукции отражено в виде операции суммирования.
Как работает сводная таблица
[править | править код]Используя вышеприведенный пример, программное обеспечение будет искать все различные значения для Региона. В этом случае: North, South, East, West. Кроме того, оно найдет все различные значения для Ship Date. В соответствии с типом агрегации, будет найдена сумма фактов и отражена на многомерном графике. В примере, первое значение искомой информации — 66. Это число было получено в результате поиска всех записей, где значением региона был East и Ship Date был равен 1/31/2005. Затем все единицы продукции из полученной коллекции (ячейки с E2 до E7), были просуммированы в финальный результат.
Сводные таблицы не создаются автоматически. Для начала нужно выбрать все данные в исходной таблице (например, в MS Excel), затем найти функцию вставки сводной таблицы. Это создаст список полей сводной таблицы. Например, если у нас есть таблица, отражающая данные по продажам компании, которые включают дату продажи, имя продавца, название проданной единицы, её цвет, количество проданных единиц, цену для каждой и суммарную цену.
Новые поля появятся на правой стороне листа. По умолчанию, под этим списком будет размещен макет дизайна сводной таблицы. Каждое из полей списка можно будет перетаскивать на этот макет, который будет содержать четыре опции:
— Фильтр отчетов;
— Название столбцов;
— Название строк;
— Суммирование значений.
Фильтр Отчёта
[править | править код]«Фильтр Отчёта» используется в Excel для применения определенного фильтра ко всей таблице. Например, если перетащить поле «Цвет элемента» в данную область, то над созданной таблицы появятся фильтры. Этот фильтр имеет выпадающие списки («Чёрный», «Красный» и «Белый» как в приведённом выше примере). При выборе определенной опции из списка (например «Чёрный»), видимая область таблицы будет содержать данные только тех строк, в которых «Цвет Элемента = Чёрный».
Названия столбцов
[править | править код]«Названия Столбцов» используется в Excel для применения фильтра к одному или нескольким столбцам, которые должны быть представлены в сводной таблице. Например, если мы перетащим поле «Sales Person» в данную область, то построенная таблица будет иметь значения из столбца «Sales Person», то есть число столбцов будет равно числу «Sales Person». Также будет добавлен столбец «Total». Касательно вышеприведенного примера, оператор создаст в таблице 3 столбца — «Harry», «Larry» и «Grand Total». Над данными появится фильтр «Названия Столбцов», где можно выбрать или отменить выбор конкретного менеджера по продажам для сводной таблицы. Таблица не будет содержать ни численных величин, ни выбранных числовых полей, но при выборе таковых, происходит автоматическое обновление столбца " Grand Total ".
Названия строк
[править | править код]«Названия Строк» используется в Excel для применения фильтра к одной или нескольким строкам, которые должны быть представлены в сводной таблице. Например, если мы перетащим поле «Sales Person» в данную область, то построенная таблица будет иметь значения из столбца «Sales Person», то есть число строк будет равно числу «Sales Person». Также будет добавлена строка «Total». Касательно вышеприведенного примера, данный оператор создаст в таблице 3 строки — «Harry», «Larry» и «Grand Total». Над данными появится фильтр «Названия Строк», где можно выбрать или отменить выбор конкретного менеджера по продажам из сводной таблицы. Таблица не будет содержать ни численных величин, ни выбранных числовых полей, но при выборе таковых, происходит автоматическое обновление строки " Grand Total ".
Суммирование значений
[править | править код]Как правило, это имеет отношение к полям, имеющим числовые значения и которые могут быть использованы для различных типов вычислений. Однако использование текстовых значений также не было бы неправильным, но вместо суммы оператор выдаст их количество. Таким образом в вышеприведенном примере, если мы перетащим «Units Sold» в данную область вместе с со строкой «Sales Person», то тогда оператором будет добавлен новый столбец «Sum of Units Sold», содержащий значения напротив каждого продавца.
| Row Labels | Sum of Units Sold |
|---|---|
| Harry | 14 |
| Larry | 18 |
| Grand Total | 32 |
Поддержка в приложениях
[править | править код]Сводные таблицы в настоящее время рассматриваются как неотъемлемая часть электронных таблиц. Конкурирующие с Microsoft Excel программы, такие как LibreOffice Calc, обеспечивают схожий функционал; в OpenOffice.org и LibreOffice вплоть до версии 3.3 сводная таблица называлась DataPilot, а начиная с версии 3.4 DataPilot был переименован в «Сводную Таблицу». Другие компании, такие как Quantrix и numberGo, также обеспечивают схожую функциональность.
Сводная таблица также реализуется как часть инструментов визуализации данных, например, в программах класса business intelligence (бизнес-аналитика).
Google Docs позволяет создавать простые сводные таблицы посредством специальных приложений-гаджетов Panorama Analytics, но к 2011 году их функциональность все еще оставалась ограниченной. В мае 2011 года компания Google объявила о развертывании изначально размещенной функции сводных таблиц в электронных таблицах редактора Google[4].
Ajax платформа ZK также позволяет встраивание сводных таблиц в Веб-приложениях.
PostgreSQL, свободная объектно-реляционная система управления базами данных (СУБД), позволяет создавать сводные таблицы, используя модуль tablefunc[5].
Сводная таблица как клиент к OLAP
[править | править код]Сводные таблицы в Excel включают в себя функцию прямых запросов к OLAP-серверу для получения данных вместо их агрегирования из таблиц Excel. В такой конфигурации сводная таблица является простым клиентом OLAP-сервера. Сводная таблица в Excel позволяет подключаться не только к решениям от Microsoft (Analysis Service), но и к любому XML совместимому для аналитики (XMLA[англ.], стандарта OLAP) серверу.
Существуют и другие клиенты OLAP-серверов: JPivot, Dundas, IcCube (Клиентская библиотека).
Примечания
[править | править код]- ↑ United States Trademark Serial Number 74472929 (27 декабря 1994). Дата обращения: 17 февраля 2013. Архивировано 4 апреля 2013 года.
- ↑ Jelen, Bill[англ.]; Alexander, Michael. Pivot table data crunching. — Indianapolis: Que, 2006. — С. 274. — ISBN 0-7897-3435-4.
- ↑
Edholm, Yorgen H.; Edholm; McNall, Kristen N.; Lew, Karl M., Patent #5915257, Дата обращения: 16 февраля 2010
{{citation}}:|first1=пропущен|last1=(справка) - ↑ Docs Blog: Summarize your data with pivot tables. Дата обращения: 25 марта 2014. Архивировано 2 декабря 2014 года.
- ↑ PostgreSQL: Documentation: 9.2: tablefunc. Дата обращения: 25 марта 2014. Архивировано 9 марта 2018 года.